package server;

import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import java.io.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;

/**
 * Created by IntelliJ IDEA.
 * User: sam
 * Date: 25/06/12
 * Time: 19:44
 * To change this template use File | Settings | File Templates.
 */

public class UploadServlet extends MFHttpServlet {

    String UPLOAD_LOCATION;
    private StringBuilder strResp;
    private int numUploads = 0; //OUCH!!! Only bit of domain/meta data we store at class level... hack
    private Document requestXML;
    private Connection connection;

    public UploadServlet(){

    }

    @Override
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        System.out.println("UploadServlet.doPost()"+"\n");

        connection = (Connection) getServletContext().getAttribute("db_connection");

        requestXML = (Document) request.getAttribute("request_xml");

        UPLOAD_LOCATION = (String) request.getAttribute("upload_location");

        processUpload(request, response);

        connection = null;
    }

    public void processUpload(HttpServletRequest request, HttpServletResponse response) {
        DiskFileItemFactory fileItemFactory = new DiskFileItemFactory();

        System.out.println("UploadServlet.processUpload(), request "+request.toString());

            ServletFileUpload uploadHandler = new ServletFileUpload(fileItemFactory);

            //TODO PrintWriter not used, in fact MFProgressListener not used.
            //TODO relying on Client side bytes written for progress bar.
            //TODO could still be useful here to know when upload complete though?

            //String UPLOAD_LOCATION = "/home/sam/mf_server_dump/music_finder_uploads"+File.separator;
            //String UPLOAD_LOCATION = "/media/My\\ Book/music_finder_uploads"+File.separator;

            //Environment.getExternalStorageState()
            //FileSystemView.getFileSystemView()

            PrintWriter printWriterOut = null;

            // cant get response.getWriter() because we need it later, can only call once.

        /**
         * **************** UPLOAD NOTES *******************
         *
         * ServletFIleUPload / httpCLient probably blocks background stream until whole file is uploaded (whole file = all multi parts too):
         * So progress 0-100% is for both file-parts.
         * Dont need to write progress to print writer.  Could just check internally for 100%, but as it probably blocks, you dont need to check if its done.
         * can just write XML response at end of upload method, as db writing and file writing will all have to have completed.
         */

            /*
            if(uploadHandler.getProgressListener() == null) {



                try {
                    printWriterOut = response.getWriter();
                    System.out.println(strResp.toString());
                    printWriterOut.println(strResp.toString());
                } catch (IOException e) {
                    System.out.println(e.toString());
                    e.printStackTrace();
                }

                MFProgressListener progListener = new MFProgressListener(printWriterOut);
                uploadHandler.setProgressListener(progListener);
            }
            */

            //strResp = new StringBuilder();
            //System.out.println("progListener.getPercentDone() "+progListener.getPercentDone());
            //strResp.append(progListener.getPercentDone());
            //write(response);
		    // Set the size threshold, above which content will be stored on disk.

            // WARNING too low/ small.  this ended up creating an empty file!!!

		    fileItemFactory.setSizeThreshold(4*1024*1024); //4 MB
            System.out.println("UploadServlet.processUpload() "+UPLOAD_LOCATION);
            fileItemFactory.setRepository(new File(UPLOAD_LOCATION));


		    // Set the temporary directory to store the uploaded files of size above threshold.

            // TODO change permission?
            // TODO change to app server context path?
            // TODO dir should be repository name if multiple repositories exist?

            //File inDir = new File("/home/sam/www/musicalms.samwootton.com/mf_user_uploads/");
            //File inFile = new File(inDir+"infile.zip");
            //File inDir = new File("/home/sam/mf_server_dump/");
		    //fileItemFactory.setRepository(inDir);

            try {

                System.out.println("request "+request);

                List items = uploadHandler.parseRequest(request);

                System.out.println("items.size() "+items.size() +"\n");

                //Map<String, String> conflictingUploads = new HashMap<String, String>();

                //Vector<UploadConflict> conflictingUploads = new Vector<UploadConflict>();

                Iterator iter = items.iterator();

                do {

                    // Appears to process in order of multipartEntity.addPart() in client (PostData).
                    // UploadInfo processed first, then UploadData.

                    FileItem item = (FileItem) iter.next();
                    String fieldName = item.getFieldName();

                    //System.out.println("item.isFormField() " + item.isFormField()+"\n");

                    if(fieldName.equals("userUploads")) {

                        // serverFileName = context.getRealPath("music/"+targetFileName);
                        // File file = new File(serverFileName);

                        System.out.println("{}{}{}{}{}{}{}{}{}{}{}{}{}{} processUpload: 'userUploads' "+fieldName+"\n");

                        try {
                            ServletContext context = getServletContext();
                        } catch(Exception e) {
                            e.printStackTrace();
                            System.out.println(e.toString());
                        }


                        //String serverFileName = context.getRealPath(item.getName());

                        File inFile = new File(UPLOAD_LOCATION+item.getName()+"\n");

                        //File inFile = new File("/home/sam/mf_server_dump/music_finder_uploads"+File.separator+item.getName()+"\n");   // inDir+"/"+item.getName()

                        //System.out.println("data Upload Field Name = "+item.getFieldName());
						//System.out.println("data Upload File Name = "+item.getName());
						//System.out.println("data Upload Content type = "+item.getContentType());
						//System.out.println("data Upload File Size = "+item.getSize());
                        //System.out.println("data Upload In Memory = "+item.isInMemory());

                        //TODO had to set permissions of webserver to 777.
                        // We still need this even with ZipFile creation below, otherwise we
                        // get "java.util.zip.ZipException: error in opening zip filejava.util.zip.ZipException:
                        // error in opening zip file"
                        item.write(inFile);

                        //InputStream inStream = item.getInputStream();
                        //FileOutputStream outStream = new FileOutputStream(inFile.getAbsolutePath());

                        ZipFile zipFile = new ZipFile(inFile.getAbsolutePath());    // could use item.getName() directly?

                        //ZipInputStream zipInputStream = new ZipInputStream(new BufferedInputStream(new FileInputStream(zipFile.getName())));

                        Enumeration tmpEnum = zipFile.entries();

                        Vector<ZipEntry> entries = new Vector<ZipEntry>();

                        while(tmpEnum.hasMoreElements()) {

                            ZipEntry ze = (ZipEntry) tmpEnum.nextElement();

                            entries.add(ze);
                        }

                        Iterator zipIt = entries.iterator();

                        int BYTES_DOWNLOAD = 1024;
                        int read=0;
                        int bytesTotal = 0;

                        do{

                            ZipEntry entry = (ZipEntry) zipIt.next();

                            FileOutputStream out = null;
                            InputStream zipInputStream = null;

                            try {

                                //out = new FileOutputStream("/home/sam/www/musicalms.samwootton.com/mf_user_uploads/"+entry.getName());
                                //File tmpTrack = new File("/home/sam/uploaded_music/"+entry.getName());
                                //File tmpTrack = new File("/home/sam/tomcat/webapps/musicfinder/music/"+entry.getName());

                                //File tmpTrack = new File("/home/sam/mf_server_dump/music_finder_uploads"+File.separator+entry.getName());  // "/home/sam/mf_server_dump/"+

                                // TODO output in logs might not reflect order of multipart upload
                                // client sends XML first, but logs are muddled & diff to tell what gets
                                // processed when.

                                // If we have conflict and upload fails, we do not rename and move in to
                                // permanent storage

                                // remove TEMPORARY_UPLOAD_HOLD if written to DB
                                // how can we tell what completes first, DB writing or upload processing????
                                String TEMPORARY_UPLOAD_HOLD = "tmp";

                                System.out.println("Writing: "+UPLOAD_LOCATION+entry.getName());


                                File tmpTrack = new File(UPLOAD_LOCATION+entry.getName());

                                // if file already exists, dont write

                                if(! tmpTrack.exists()) {

                                    //File tmpTrack = new File("/home/sam/mf_server_dump/music"+File.separator+entry.getName());

                                    out = new FileOutputStream(tmpTrack.getAbsolutePath());

                                    //TODO what about item.getInputStream() straight from upload??????????????????????????????

                                    zipInputStream = zipFile.getInputStream(entry);
                                } else {
                                    System.out.println("FILE CONFLICT " + entry.getName() + " already exists. Not adding to zipInputStream!!!!!!");
                                }

                            } catch (FileNotFoundException e) {
                                e.printStackTrace();
                            } catch (IOException e) {
                                e.printStackTrace();
                            }


                            byte[] bytes = new byte[BYTES_DOWNLOAD];

                            if(zipInputStream != null ) {
                                try {
                                    while((read = zipInputStream.read(bytes)) != -1) {
                                        out.write(bytes, 0, read);
                                        bytesTotal+= read;
                                    }

                                    System.out.println("bytesTotal "+bytesTotal+"\n");

                                } catch (IOException e) {
                                    e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                                }
                            } else {
                                System.out.println("zipInputStream was null - tmpTrack.exists() must have been true \n");
                            }

                        }while(zipIt.hasNext());

                        if(inFile != null ) {
                            inFile.delete();
                        }

                    } else if(fieldName.equals("uploadInfo")) {

                        System.out.println("{}{}{}{}{}{}{}{}{}{}{}{}{}{} processUpload: 'uploadInfo' "+fieldName+"\n");

                        //File inFile = new File("/home/sam/mf_server_dump/music_finder_uploads"+File.separator+item.getName());
                         File inFile = new File(UPLOAD_LOCATION+item.getName());

                        //File inFile = new File("/home/sam/mf_server_dump/music_finder_uploads/server_uploadfile.zip");

                        //System.out.println("info Upload Field Name = "+item.getFieldName());
						//System.out.println("info Upload File Name = "+item.getName());
						//System.out.println("info Upload Content type = "+item.getContentType());
						//System.out.println("info Upload File Size = "+item.getSize());

                        item.write(inFile);

                        // if(! tmpTrack.exists()) above, so havent saved duplicate file paths to file system.
                        // but conflicts might be different file path/name, but resulted in conflict
                        // due to artist name or track name....

                        Vector<UploadConflict> conflicts = writeUploadsToDB(request, response, inFile);

                        System.out.println("conflicts: "+conflicts.size());

                        //... so we still might have to remove some (that got written)
                        // but did have a DB conflict

                        StringBuilder strResp = new StringBuilder();
                        strResp.append("<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n");
                        strResp.append("<MFResponse>\n");
                        strResp.append("<Response type='Upload' conflicts='"+conflicts.size()+"/"+numUploads+"'>\n");

                        if(conflicts.size() > 0) {
                            Iterator itr = conflicts.iterator();

                            while(itr.hasNext()) {

                                UploadConflict uploadConflict = (UploadConflict) itr.next();

                                String conflictFile = uploadConflict.getFile();
                                String conflictCode = uploadConflict.getType();
                                String conflictName = uploadConflict.getName();
                                String conflictArtist = uploadConflict.getArtist();

                                strResp.append("<Upload worked='no' " +
                                        "conflict_type='"+ conflictCode +"' " +
                                        "name='"+conflictName+"' " +
                                        "artist='"+conflictArtist+"' " +
                                        "file='"+conflictFile+"'/>\n");

                                // ************ CouldNotLoadArgumentException USE FileItem.EXISTS CHECK FOR PATH CONFLICT
                                // ************ but going toString() use DB for all conflicts instead

                                /*
                                File tmpFile = new File(filePath);
                                if(tmpFile.exists()) {
                                    // Could just check for "FILE_NAME_CONFLICT", as "TRACK_ALREADY_UPLOADED"
                                    // should have been covered with first "if(! tmpTrack.exists())" when reading upload stream

                                    boolean success = tmpFile.delete();
                                    System.out.println("Deleted :"+success);
                                }
                                */
                            }
                        }

                        strResp.append("</Response>\n");
                        strResp.append("</MFResponse>\n");

                        response.setHeader("MFServer-Error", "false");
                        System.out.println("write: "+strResp.toString());
                        write(response, strResp);
                    }

                    /*
                    InputStream is = item.getInputStream();
                    InputStreamReader isr = new InputStreamReader(is);

                    File inFile = new File(inDir+"/infile.zip");

                    FileOutputStream fos = new FileOutputStream(inFile);

                    int BYTES_DOWNLOAD = 1024;
                    int read=0;
                    bytesTotal = 0;
                    byte[] bytes = new byte[BYTES_DOWNLOAD];


                    while((read = is.read(bytes)) != -1) {

                        fos.write(bytes, 0, read);
                        bytesTotal+= read;

                        System.out.println("bytesTotal "+bytesTotal);
                        //double tmpLong = bytesTotal / contentLength;
                        //progress = (long) (tmpLong * 100);    // e.g. (15092736 / 29037252)* 100 = 52%
                        //setProgress((int)progress);
                    }
                    */


                } while (iter.hasNext());

            }catch(FileUploadException e1) {
                e1.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
            }

            // never reached or cant do - out already being written to for progress bar?

        //response.setHeader("MFServer-Error", "false");
        //System.out.println("write: "+strResp.toString());
        //write(response, strResp);
    }

    public Vector<UploadConflict> writeUploadsToDB(HttpServletRequest request, HttpServletResponse response, File uploadData) {

        System.out.println("writeUploadsToDB()");

        /**
         *
         * ALTER TABLE mf_tracks ADD runtime DOUBLE DEFAULT 2.50
         * ALTER TABLE mf_tracks DROP runtime;

           could be DECIMAL

         but 0.00 is stored as 0, so 2.50 is stored as 2.5... need to add 0 or 00 on to times.

         * File soundFile = null;
        AudioInputStream audioInputStream = null;

        try {
            audioInputStream = AudioSystem.getAudioInputStream(soundFile);
        } catch (UnsupportedAudioFileException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        AudioFormat format = audioInputStream.getFormat();
        long audioFileLength = soundFile.length();
        int frameSize = format.getFrameSize();
        float frameRate = format.getFrameRate();
        float durationInSeconds = (audioFileLength / (frameSize * frameRate));
         */

        Vector<UploadConflict> conflicts = new Vector<UploadConflict>();
        //Map<String, String> conflictingUploads = new HashMap<String, String>();

        // PROBLEM: valid/new entries can get inserted even if others fail (if failure is after valid insert).
        // also if there are multiple failures, we SQLException is thrown on first one, not others.
        // So how can we report back to the user which tracks failed, and why?
        try {

            //String query = new String();

            String username = request.getSession().getAttribute("username").toString();
            int userID = Integer.parseInt(request.getSession().getAttribute("userID").toString());
            int repositoryID = Integer.parseInt(request.getSession(false).getAttribute("userRepository").toString());
            int genreID;

            System.out.println("userID "+userID);
            System.out.println("repositoryID "+repositoryID);



            //TODO check that track && artist doesn't already exist
            // store tracks in genre sub folders?
            // artist && track && file name

            /**
             *  INSERT INTO mf_tracks(name, artist, file, user, repository)
                SELECT "01 Closer", "Kings of Leon", "01 Closer - Kings of Leon.mp3", 1, 3
                FROM dual
                WHERE NOT EXISTS
                (SELECT name, artist, file
                FROM mf_tracks
                WHERE name = "01 Closer" AND artist = "Kings of Leon" OR file = "01 Closer - Kings of Leon.mp3");
             */

            DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
            DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
            FileInputStream fis = new FileInputStream(uploadData);

            Document doc = dBuilder.parse(new InputSource(fis));

            NodeList nList = doc.getElementsByTagName("Track");
            Node nNode = nList.item(0);
            Element eElement = (Element) nNode;

            String strBuilder = username + " has donated the following tracks to music-alms:"+"\n"+"\n";

            System.out.println("username "+username);

            /**
            SELECT name, artist, file
            FROM mf_tracks WHERE(
            (name='01 Closer' AND artist='Kings of Leon')
            OR (name='4 strings' AND artist='take me away')
            )
            OR file='01 Closer - Kings of Leon.mp3'
            OR file='4 strings - take me away.mp3';
             */

            //String CONFLICT_QUERY = "SELECT name, artist, file FROM mf_tracks WHERE";
            //String NAME_ARTIST_CONFLICT_QUERY = "(";
            //String FILE_CONFLICT_QUERY = "";

            numUploads = nList.getLength();

            for (int i = 0; i < numUploads; i++) {

                nNode = nList.item(i);

                if (nNode.getNodeType() == Node.ELEMENT_NODE) {

                    eElement = (Element) nNode;
                    eElement.normalize();

                    String trackFile = eElement.getAttribute("file");
                    String trackName = eElement.getAttribute("name");
                    String trackArtist = eElement.getAttribute("artist");
                    String trackGenre = eElement.getAttribute("genre");
                    String trackType = eElement.getAttribute("type");

                    genreID = getGenre(trackGenre);

                    System.out.println("\n"+"trackFile: "+trackFile);
                    System.out.println("trackName: "+trackName);
                    System.out.println("trackArtist: "+trackArtist);
                    System.out.println("trackGenre: "+trackGenre);
                    System.out.println("trackType: "+trackType+"\n");

                    /**
                     * INSERT INTO mf_tracks(genre, name, artist, user, file, repository, runtime) SELECT
                     * 1, '01 Closer', 'Kings of Leon', 1, '01 Closer - Kings of Leon.mp3', 1, 0.0
                     * FROM dual WHERE NOT EXISTS (SELECT name, artist, file FROM mf_tracks WHERE
                     * file='01 Closer - Kings of Leon.mp3');
                     *
                     * SELECT LAST_INSERT_ID();
                     */


                    String uploadResponse = writeSingleUpload(genreID, trackName, trackArtist, userID, trackFile, repositoryID, 0.00);

                    if(uploadResponse.equals("SUCCESS")) {

                        //updateGenrePostTrackInsert(genreID, repositoryID);

                        strBuilder += trackName + ", by " + trackArtist + " (" + trackGenre + ")"+"\n";

                    } else if(uploadResponse.equals("UPLOAD_CONFLICT")) {

                        UploadConflict conflict = new UploadConflict(trackName, trackArtist, trackFile, uploadResponse);
                        conflicts.add(conflict);

                    } else if(uploadResponse.equals("FILE_CONFLICT")) {

                        UploadConflict conflict = new UploadConflict(trackName, trackArtist, trackFile, uploadResponse);
                        conflicts.add(conflict);

                    } else if(uploadResponse.equals("UNKNOWN_INSERT_ERROR")) {

                    }
                }

                    /*
                    NAME_ARTIST_CONFLICT_QUERY += "(name='" + trackName +"' AND artist='"+trackArtist+"')";
                    FILE_CONFLICT_QUERY += " OR file='" + trackFile + "'";
                    if( i+1 < nList.getLength() ) {
                        NAME_ARTIST_CONFLICT_QUERY += " OR ";
                    } else {
                        NAME_ARTIST_CONFLICT_QUERY += ")";
                        FILE_CONFLICT_QUERY += ";";
                    }
                    */

                    // 2012-02-25 11:56:58 2012-04-22 10:05:02.843
                    //Calendar calendar = Calendar.getInstance();
                    //java.sql.Timestamp timeStamp = new java.sql.Timestamp(calendar.getTime().getTime());
                    //Date date1 = new Date(new Timestamp(System.currentTimeMillis()).getTime());
                    //java.util.Date now = new java.util.Date();
                    //Date date2 = new Date(new Timestamp(now.getTime()).getTime());
                    //SimpleDateFormat formatter = new SimpleDateFormat("yyyy.MM.dd ww hh:mm:ss a zzz");
                    //System.out.println("date1 "+date1);
                    //System.out.println("date2 "+date2);
                    //System.out.println("timeStamp "+timeStamp);

                    //INSERT INTO mf_tracks(genre, name, artist, user, uploaded, file) VALUES('Dance','Kings of Leon.mp3','01 Closer','1',null,'01 Closer - Kings of Leon.mp3');

                    // TODO session / cookie management to get user ID / name?
                    // TODO get user and genre id's
                    // TODO re run schema: uploaded TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    // TODO change mf_tracks schema to have track type e.g. mp3 / wmv

                    // TODO what if query is empty cos all (might only be single upload) uploads conflicted?
                    // e.g. INSERT INTO mf_tracks(genre, name, artist, user, uploaded, file, repository) VALUES;



                    //if(!conflictType.equals("")) {

                        //query += "('"+genreID+"','" +trackName+"','"+trackArtist+"','"+userID+"',"+null+ ",'"+trackFile+"',"+repositoryID+")"+"\n";

                        // (i+1) + ") " +

                        //strBuilder += trackName + ", by " + trackArtist + " (" + trackGenre + ")"+"\n";

                        /*if(i+1 < nList.getLength()) {
                            query += ",";
                        } else {
                            query += ";";
                        }*/

                    //} else {
                         //System.out.println("UploadConflict "+CONFLICT_QUERY);
                        //UploadConflict uploadConflict = new UploadConflict(trackName, trackArtist, trackFile, conflictType);
                        //conflicts.add(uploadConflict);
                    //}

                    // Hack, 3rd param is actually contributor, but using it as genre for data passing only
                    //Track tmpTrack = new Track(trackName, trackArtist, trackGenre, "foo", "foo", "foo");

                    //tmpData[i] = new Track(trackName, trackArtist, trackGenre, "foo", "foo", "foo");
                    //tmpData.add(new Track(trackName, trackArtist, trackGenre, "foo", "foo", "foo"));
                }

                if(conflicts.size() < nList.getLength()) {  // have 1 or more successful uploads

                    notifyUsersOfActivity(strBuilder.toString(), request.getSession(false));

                } else {

                    System.out.println("WARNING: ********* ~~~~~~~~~~~~~~~~~~~~~~ *********");
                    System.out.println("WARNING: ********* All uploads conflicted *********");
                    System.out.println("WARNING: ********* ~~~~~~~~~~~~~~~~~~~~~~ *********");
                }

            } catch (ParserConfigurationException e) {
            e.printStackTrace();
        } catch (SAXException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return conflicts;
    }
    /*
    private void updateGenrePostTrackInsert(int genreID, int repositoryID) {

        String query = "UPDATE mf_genres SET updated=CURRENT_TIMESTAMP, num_tracks=num_tracks+1 WHERE id='"+genreID+"' AND repository='"+repositoryID+"'";

        try {
            Statement db_st = connection.createStatement();
            int tmp = db_st.executeUpdate(query.toString());
        }catch (SQLException e) {
            System.out.println("updateGenrePostTrackInsert.SQLException");
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
    */

    private void updateGenrePostTrackInsert(int genreID, int repositoryID) {

        String query = "UPDATE mf_genres SET updated=CURRENT_TIMESTAMP, num_tracks=num_tracks+1 WHERE id='"+genreID+"' AND repository='"+repositoryID+"'";

        try {
            Statement db_st = connection.createStatement();
            int tmp = db_st.executeUpdate(query.toString());
        }catch (SQLException e) {
            System.out.println("updateGenrePostTrackInsert.SQLException");
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }

    private int getGenre(String genreName) {

        String query = new String();
        query += "select id from mf_genres where name='"+genreName+"';";

        String genreID = "";

        try {
                //Class.forName("com.mysql.jdbc.Driver");  //registering the jdbc driver here
                //db_con = DriverManager.getConnection(db_url, db_user, db_pwd);



                Statement db_st = connection.createStatement();
                ResultSet db_rs = db_st.executeQuery(query);

                while (db_rs.next()) {
                    genreID = db_rs.getString(1);
                }

            } catch (SQLException e2) {

                System.out.println(e2.getMessage());
                e2.printStackTrace();
            }

        return Integer.parseInt(genreID);
    }

    private String writeSingleUpload(int genreID, String trackName, String trackArtist, int userID, String trackFile, int repositoryID, double runTime) {

        System.out.println("UploadServlet.writeSingleUpload()");
        System.out.println("UploadServlet.writeSingleUpload() genreID "+genreID);
        System.out.println("UploadServlet.writeSingleUpload() trackName "+trackName);
        System.out.println("UploadServlet.writeSingleUpload() trackArtist "+trackArtist);
        System.out.println("UploadServlet.writeSingleUpload() userID "+userID);
        System.out.println("UploadServlet.writeSingleUpload() trackFile "+trackFile);
        System.out.println("UploadServlet.writeSingleUpload() repositoryID "+repositoryID);
        System.out.println("UploadServlet.writeSingleUpload() runTime "+runTime);

        String responseCode = "SUCCESS";

        /*String query = "INSERT INTO mf_tracks(genre, name, artist, user, file, repository, runtime) SELECT ";
        query += genreID + ", '" + trackName + "', '" + trackArtist +"', " + userID + ", '"+trackFile+"', "+ repositoryID + ", "+0.00;
        query +=" FROM dual WHERE NOT EXISTS (SELECT name, artist, file FROM mf_tracks WHERE ";
        query +="file='"+trackFile+"');";*/

        String query = "INSERT INTO mf_tracks(genre, name, artist, user, file, repository) SELECT ";
        query += genreID + ", '" + trackName + "', '" + trackArtist +"', " + userID + ", '"+trackFile+"', "+ repositoryID;
        query +=" FROM dual WHERE NOT EXISTS (SELECT name, artist, file FROM mf_tracks WHERE ";
        query +="file='"+trackFile+"');";

                try {

                    Statement db_st = connection.createStatement();

                    int tmp = db_st.executeUpdate(query.toString());

                    ResultSet db_rs = db_st.executeQuery("SELECT LAST_INSERT_ID();");

                    while(db_rs.next()) {
                        int insertID = db_rs.getInt(1);

                        System.out.println("SELECT LAST_INSERT_ID() "+insertID);

                        if(insertID == 0) {
                            responseCode = "FILE_CONFLICT";
                        }
                    }


                    // TODO when should be close resultsets?
                    // When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

                    //TODO do not execute if previous insert failed
                    // Warning: Printwriter already got for progress listener
                    //doGenreRequest(request, response);

                    // okay, we're using (false), but should be okay, cos we want to throw error
                    // if user doesnt have session. and we also do a pre-check before uploading call
                }
                catch(MySQLIntegrityConstraintViolationException e) {

                    System.out.println("MySQLIntegrityConstraintViolationException");

                    if(e.getMessage().contains("for key 'uc_tracks'")) { // Duplicate entry
                        responseCode = "UPLOAD_CONFLICT";
                        // ERROR 1062 (23000): Duplicate entry '01 Closer-Kings of Leon-1' for key 'uc_tracks'
                    }

                    System.out.println(e.getMessage());
                    e.printStackTrace();
                }
                catch (SQLException e) {

                    System.out.println("SQLException");

                    responseCode = "UNKNOWN_INSERT_ERROR";

                    if(e.getMessage().contains("for key 'uc_tracks'")) { // Duplicate entry
                        responseCode = "UPLOAD_CONFLICT";
                        // ERROR 1062 (23000): Duplicate entry '01 Closer-Kings of Leon-1' for key 'uc_tracks'
                    }

                    System.out.println(e.getMessage());
                    e.printStackTrace();

                } finally {

                    System.out.println("responseCode: " + responseCode);
                }

        return responseCode;

    }
}
